package com.wd.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.Map.Entry;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.log4j.PropertyConfigurator;

import com.sun.media.sound.HsbParser;
import com.wd.model.AlexaDataReference;
import com.wd.util.DataUtil;

public class AlexaDAOImpl {

	private static Log log = LogFactory.getLog(AlexaDAOImpl.class);

	public boolean insertAlexaDataReference(AlexaDataReference model) {
		Connection con = null;
		PreparedStatement st = null;
		String sql = "insert into alexa_data_reference(ent_id, traffic_rank,[3rd_m_growth_movement],traffic_rank_cn, site_linking_in, avg_load_time, pageviews_global_percent, pageviews_per_user, se_referer_percentage,sub_domain_spread,input_date) values(?,?,?,?,?,?,?,?,?,?,?)";
		if(log.isDebugEnabled()){
			log.debug(sql);
		}
		con = DAO.getInstance().getConnection();
		try {
			st = con.prepareStatement(sql);
			int i = 1;
			st.setInt(i++, model.getEnt_id());
			st.setInt(i++, model.getTraffic_rank());
			st.setInt(i++, model.getThree_m_growth_movement());
			st.setInt(i++, model.getTraffic_rank_cn());
			st.setInt(i++, model.getSite_linking_in());
			st.setFloat(i++, model.getAvg_load_time());
			st.setFloat(i++, model.getPageviews_global_percent());
			st.setFloat(i++, model.getPageviews_per_user());
			st.setFloat(i++, model.getSe_referer_percentage());
			st.setString(i++, model.getSub_domain_spread());
			st.setString(i++, model.getInput_date());
			st.executeUpdate();
		} catch (SQLException e) {
			log.error(e);
		} finally {
			DAO.close(con, st);
		}
		return true;
	}

	public Map<Integer, String> getEntIdMap() {
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		String sql = "select ent_Id,notes from ent_info where ent_id != 0 and (ent_type = 1 or ent_type = 3)";
		if(log.isDebugEnabled()){
			log.debug(sql);
		}
		con = DAO.getInstance().getConnection();
		Map<Integer, String> entIdMap = new HashMap<Integer, String>();
		try {
			st = con.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				entIdMap.put(rs.getInt("ent_id"), rs.getString("notes"));
			}
		} catch (SQLException e) {
			log.error(e);
		} finally {
			DAO.close(con, st, rs);
		}
		return entIdMap;
	}

	public List<AlexaDataReference> getAlexaDataReferences(String inputDate,
			int entType) {
		Connection con = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		AlexaDataReference model = null;
		List<AlexaDataReference> list = new ArrayList<AlexaDataReference>();
		String sql = "select a.traffic_rank,a.traffic_rank_cn,"
				+ "a.[3rd_m_growth_movement],"
				+ "a.avg_load_time,"
				+ "a.pageviews_per_user,"
				+ "a.se_referer_percentage,"
				+ "a.site_linking_in,a.sub_domain_spread,b.ent_id,b.ent_name"
				+ " from dbo.alexa_data_reference a inner join ent_info b on a.ent_id = b.ent_id"
				+ " where a.traffic_rank_cn != 0 and substring(a.input_date,1,8) = ?"
				+ " and b.ent_type = ? order by traffic_rank_cn asc";
		if(log.isDebugEnabled()){
			log.debug(sql);
		}
		con = DAO.getInstance().getConnection();
		try {
			st = con.prepareStatement(sql);
			st.setString(1, inputDate);
			st.setInt(2, entType);
			rs = st.executeQuery();
			while (rs.next()) {
				model = new AlexaDataReference();
				model.setTraffic_rank(rs.getInt("traffic_rank"));
				model.setTraffic_rank_cn(rs.getInt("traffic_rank_cn"));
				model.setAvg_load_time(rs.getFloat("avg_load_time"));
				model.setThree_m_growth_movement(rs.getInt("3rd_m_growth_movement"));
				model.setPageviews_per_user(rs.getFloat("pageviews_per_user"));
				model.setSe_referer_percentage(rs
						.getFloat("se_referer_percentage"));
				model.setSite_linking_in(rs.getInt("site_linking_in"));
				model.setSub_domain_spread(rs.getString("sub_domain_spread"));
				model.setEnt_id(rs.getInt("ent_id"));
				model.setEnt_name(rs.getString("ent_name"));
				list.add(model);
			}
		} catch (SQLException e) {
			log.error(e);
		} finally {
			DAO.close(con, st, rs);
		}

		return list;
	}

	public static void main(String[] args) {
		PropertyConfigurator.configure("conf/log4j/log4j.properties");
		AlexaDataReference a = new AlexaDataReference();
		AlexaDAOImpl impl = new AlexaDAOImpl();
		// a.setEnt_id(1);
		// a.setTraffic_rank(1);
		// a.setTraffic_rank_cn(1);
		// a.setSite_linking_in(1000);
		// a.setPageviews_global_percent(0.00001f);
		// a.setPageviews_per_user(0.111111f);
		// a.setSe_referer_percentage(0.0000003f);
		// a.setAvg_load_time(2.033f);
		// impl.insertAlexaDataReference(a);
		// for(int i : impl.getEntIds()){
		// System.out.println(i);
		// }
		// Set<Entry<Integer, String>> entrySet = impl.getEntIdMap().entrySet();
		// for(Entry<Integer, String> entry : entrySet){
		// System.out.println(entry.getKey() +"  "+entry.getValue());
		// }
		long start = System.currentTimeMillis();
		List<AlexaDataReference> list = (impl.getAlexaDataReferences("20091211", 3));
		for(int i = 0 ; i < list.size() ; i ++){
			System.out.println(list.get(i).getEnt_id()+"\t"+list.get(i).getEnt_name() + "\t" + list.get(i).getTraffic_rank_cn() + "\t" + DataUtil.getFormatData(list.get(i).getPageviews_global_percent(), 6));
		}
		long end = System.currentTimeMillis();
		System.out.println(end - start);
	}
}
